package icu.groot.alan.dao.impl;

import icu.groot.alan.dao.DepartmentDao;
import icu.groot.alan.entity.Department;
import icu.groot.alan.entity.Paging;
import icu.groot.alan.util.Dao;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDaoImpl implements DepartmentDao {
    @Override
    public void paging(Paging<Department> paging, Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Department department = null;
        List<Department> departments = new ArrayList<>();
        List<Object> params = new ArrayList<>();

        String sql = "SELECT d.`name` department, d.id id, COUNT(u.code) userCount FROM `department` d LEFT JOIN `user` u ON u.department_id = d.id WHERE 1 = 1";

        if (!StringUtils.isAnyBlank(paging.getFuzzyQuery())){
            sql += " and d.name like ? ";
            params.add("%" + paging.getFuzzyQuery() + "%");
        }
        sql += " GROUP BY d.id ";
        int pageNow = paging.getPageNow();
        if (pageNow < 0 || pageNow >= paging.getPageCount()){
            pageNow = 0;
        }
        sql += " LIMIT ?, ? ";
        params.add((paging.getPageNow() - 1) * paging.getPageSize());
        params.add(paging.getPageSize());
        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params.toArray());
            while (resultSet.next()) {
                department = new Department();
                department.setId(resultSet.getInt("id"));
                department.setName(resultSet.getString("department"));
                department.setUserCount(resultSet.getInt("userCount"));
                departments.add(department);
            }
        } catch (SQLException e) {
            paging.setData(null);
        }
        paging.setData(departments);
        Dao.closeResource(preparedStatement, resultSet);
    }

    @Override
    public int getCount(Connection connection, String fuzzyQuery) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int rowCount = 0;
        Object[] params = {};
        String sql = "SELECT count(*) rowCount from department where 1 = 1";
        if (!StringUtils.isAnyBlank(fuzzyQuery)){
            sql += " and name like ? ";
            params = new Object[]{"%" + fuzzyQuery + "%"};
        }
        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
            while (resultSet.next()) {
                rowCount = resultSet.getInt("rowCount");
            }
        } catch (SQLException e) {
            return 0;
        }
        Dao.closeResource(preparedStatement, resultSet);
        return rowCount;
    }

    @Override
    public int deleted(Connection connection, int[] idsInt) {
        PreparedStatement preparedStatement = null;
        int updateRow = 0;
        Object[] params = {};
        StringBuilder sql = new StringBuilder("DELETE FROM department WHERE id in (");
        for (int i = 0; i < idsInt.length; i++) {
            if (i != idsInt.length - 1){
                sql.append(" ").append(idsInt[i]).append(", ");
                continue;
            }
            sql.append(idsInt[i]).append(" )");
        }
        try {
            updateRow = Dao.execute(connection, preparedStatement, sql.toString(), params);
        } catch (SQLException e) {
            return 0;
        }
        Dao.closeResource(preparedStatement);
        return updateRow;
    }

    @Override
    public List<Department> getDepAll(Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Department> departments = new ArrayList<>();
        Department department = null;
        String sql = "select id, name from department";
        Object[] params = {};

        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
            while (resultSet.next()) {
                department = new Department();
                department.setId(resultSet.getInt("id"));
                department.setName(resultSet.getString("name"));
                departments.add(department);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Dao.closeResource(preparedStatement, resultSet);
        return departments;
    }

    @Override
    public int add(Connection connection, String name) {
        PreparedStatement preparedStatement = null;
        int updateRow = 0;
        Object[] params = {name};
        String sql = "insert into department(name) values(?)";

        try {
            updateRow = Dao.execute(connection, preparedStatement, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Dao.closeResource(preparedStatement);
        return updateRow;
    }
}
